-- =============================================
-- This stored procedure moves a specified ContentLink
--
-- Modifications
-- 08/28/2003  E.Tatsui   Created
-- =============================================
CREATE      PROCEDURE amsp_CMMoveContentLink
  @InMoveContentLinkID numeric,
  @InTargetContentLinkID numeric,
  @InContentID numeric
AS
BEGIN
  DECLARE
    @TargetSortOrder numeric,
    @ContentID numeric,
    @ContentLinkID numeric,
    @SortOrder numeric,
    @Counter integer,
    @TargetContentLinkID numeric,
    @MoveContentLinkID numeric

  SELECT @ContentID = ContentID,
         @TargetSortOrder = SortOrder,
         @TargetContentLinkID = ContentLinkID
    FROM Content_Link WITH (NOLOCK)
   WHERE (ContentLinkID = @InTargetContentLinkID
      OR PreviousContentLinkID = @InTargetContentLinkID)
     AND ContentID = @InContentID

  SELECT @MoveContentLinkID = ContentLinkID
    FROM Content_Link WITH (NOLOCK)
   WHERE (ContentLinkID = @InMoveContentLinkID
      OR PreviousContentLinkID = @InMoveContentLinkID)
     AND ContentID = @InContentID

  IF @TargetContentLinkID IS NULL OR @MoveContentLinkID IS NULL
    RETURN

  -- Move everything below target by one.
  UPDATE Content_Link
     SET SortOrder = SortOrder + 1
   WHERE ContentID = @ContentID
     AND SortOrder > @TargetSortOrder

  -- Move the request item right below the target.
  UPDATE Content_Link
     SET SortOrder = @TargetSortOrder + 1
   WHERE ContentLinkID = @MoveContentLinkID

  -- Now, re-sort everything.
  DECLARE c_Links CURSOR FOR
   SELECT ContentLinkID,
          SortOrder
     FROM Content_Link WITH (NOLOCK)
    WHERE ContentID = @ContentID
    ORDER BY SortOrder

  SET @Counter = 0

  OPEN c_Links
  FETCH NEXT FROM c_Links
   INTO @ContentLinkID,
        @SortOrder

  WHILE @@FETCH_STATUS = 0 BEGIN
    SET @Counter = @Counter + 1
    IF @Counter <> @SortOrder BEGIN
      UPDATE Content_Link
         SET SortOrder = @Counter
       WHERE ContentLinkID = @ContentLinkID
    END

    FETCH NEXT FROM c_Links
     INTO @ContentLinkID,
          @SortOrder
  END
  CLOSE c_Links
  DEALLOCATE c_Links
  
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMMoveContentLink] TO [IMIS]
GO